<?php
/**
 * Fix Wrong Word in the dictionnary, first version of document parsing
 * have added some words that are not correct like (----[a-Z]--} words.
 * 
 * This interface is going to search blacklisted type 1 word and delete there
 * matches
 * 
 */

// Rebuild Ressource Search Engine 
use Fox\SearchEngine\Match;
use Fox\Html\Input;
use Fox\SearchEngine;
use Fox\Html\CheckBox;
use Fox\Dao\SqlFragment;
use Hive\Ressource;
use Fox\Html\Interval;
use Fox\Html\Block;
use Fox\Html\Screen;

$xview = Xview::instance();
set_time_limit(30000);

$screen = new Screen('bexe','execute');
$screen->pageTitle('Keywords Correction');
$block = new Block('bExec','Parameters');
$block->add(new CheckBox('wrongwords'))->label('Incorrect Word deletion');
$block->add(new CheckBox('trimissue'))->label('Word with trim issue');
$block->add(new CheckBox('trimissue2'))->label('Word with trim issue 2');
$block->add(new Input('dbcnt',true,10));
$screen->add($block);

if($screen->submitted()) {
	$finder = new Ressource();
	$fields = $screen->get();
	if(!$screen->errors()) 
	{
		$sql = null;
		$commit = true;
		if($fields['trimissue']) {
			// Load all words with incorrect trim
			$query = Fox\Databases::databases()->handle(Fox\Databases::DB_APP)->createQueryBuilder();
			$query->select('t.id','t.word')
				->from('se_keywords','t')
				->setMaxResults($fields['dbcnt']);
			$fragment = new SqlFragment('black = 2');
			$words = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
			foreach($words as $word) {
				// Find if there is an existing word in the dictionnary that 
				// is equivalent (without the triming issue)
				$correctWord = SearchEngine::cleanupContent($word['word']);
				$query->select('DISTINCT t.id','t.word')->from('se_keywords','t');
				$fragment = new SqlFragment('word = ?',array($correctWord));
				$results = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
				
				if(count($results) == 1) {
					// Perfect Match
					Fox\Message::Information('One Keyword for :'.$word['word'].' - id:'.$word['id'].' -> '.$results[0]['word'].' / '.$results[0]['id'] );
					$sql .= '<br>UPDATE se_matches SET keywordid = '.$results[0]['id'].' WHERE keywordid = '.$word['id'].';';
					// Find Matches
					$query->select('DISTINCT keywordid','reference_id','reference_type')->from('se_matches','w');
					$fragment = new SqlFragment('keywordid = ?',array($word['id']));
					$matches = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
					echo '<pre>'.var_dump($matches).'</pre>';
					foreach($matches as $match) {
						try {
							$handle = Fox\Databases::databases()->handle(Fox\Databases::DB_APP);
							
							$handle->beginTransaction();
							$handle->update(
								'se_matches',
								array('keywordid' => $results[0]['id']),
								$match
							);
							$handle->commit();
							
						} catch (DBALException $e) {
							Fox\Message::Warning($e->getMessage());
							$handle = Fox\Databases::databases()->handle(Fox\Databases::DB_APP);
							$handler->delete(\Fox\Dao::infos(Match::className())->tableName(),$match);
						} catch (Exception $e) {
							Fox\Message::Warning($e->getMessage());
							$handle = Fox\Databases::databases()->handle(Fox\Databases::DB_APP);
							$handler->delete(\Fox\Dao::infos(Match::className())->tableName(),$match);
						}
					}
				} else if(count($results) > 1) {
					// Multiple Match
					$rWords = null;
					foreach($results as $result) {
						$rWords .= ' - '.$result['word'];
					}
					Fox\Message::Warning('Multiple Keywords for :'.$word['word'].' - id:'.$word['id'].' ::'.$rWords);
					
				} else {
					// No Match
					Fox\Message::Warning('No keyword for :'.$word['word'].' - id:'.$word['id']);
				}
			}
		}
		if($fields['trimissue2']) {
			// Load all words with incorrect trim
			$query = Fox\Databases::databases()->handle(Fox\Databases::DB_APP)->createQueryBuilder();
			$query->select('t.id','t.word')
				->from('se_keywords','t')
				->setMaxResults($fields['dbcnt']);
			$fragment = new SqlFragment('id = 171331');
			$words = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
			foreach($words as $word) {
				$correctWord = SearchEngine::cleanupContent($word['word']);
				echo "Word {$word['word']} -> Cor $correctWord <br>";
			}
		}
		echo $sql;
	}
}
echo $screen->display();